Importing Microsoft Access Reports

One of the most powerful features of the C1ReportDesigner application is the ability to import reports created with Microsoft Access. This feature requires Access to be installed on the computer. Once the report is imported into the Designer, Access is no longer required.

To import reports from an Access file, click the Application button and select Import from the menu. A dialog box prompts you for the name of the file you want to import.

Select a Microsoft Access file (MDB or ADP) and the Designer scans the file and shows a dialog box where you can select which reports you would like to import:

The dialog box also allows you to specify if the Designer should clear all currently defined reports before starting the import process.

The import process handles most elements of the source reports, with a few exceptions:

Event handler code

Access reports can use VBA, macros and forms to format the report dynamically. C1.C1Report.C1Report can do the same things, but it only uses VBScript. Because of this, all report code needs to be translated manually.

Form-oriented field types

Access reports may include certain fields that are not handled by the Designer's import procedure. The following field types are not supported: Chart, CommandButton, ToggleButton, OptionButton, OptionGroup, ComboBox, ListBox, TabCtl, and CustomControl.

Reports that use VBScript reserved words

Because Access does not use VBScript, you may have designed reports that use VBScript reserved words as identifiers for report objects or dataset field names. This causes problems when the VBScript engine tries to evaluate the expression, and prevents the report from rendering correctly.

Reserved words you shouldn't use as identifiers include Date, Day, Hour, Length, Minute, Month, Second, Time, TimeValue, Value, Weekday, and Year. For a complete list, please refer to a VBScript reference.

Reports that sort dates by quarter (or weekday, month of the year, and so on)

C1.C1Report.C1Report uses the ADO.NET dataset C1.C1Report.Group.Sort property to sort groups. This property sorts datasets according to field values only and does not take expressions. (Note that you can group according to an arbitrary expression, but you can't sort.) An Access report that sorts groups by quarter will sort them by date after it is imported. To fix this, you have two options: create a field that contains the value for the expression you want to sort on or change the SQL statement that creates the dataset and perform the sorting that way.

These limitations affect a relatively small number of reports, but you should preview all reports after importing them, to make sure they still work correctly.

Importing the Nwind.mdb File

To illustrate how the Designer fares in a real-life example, try importing the Nwind.mdb file. It contains the following 13 reports. (The Nwind.xml file that ships with C1.C1Report.C1Report already contains all the following modifications.)

Alphabetical List of Products

No action required.

Catalog

No action required.

Customer Labels

No action required.

Employee Sales by Country

This report contains code which needs to be translated manually. The following code should be assigned to the Group 1 Header C1.C1Report.Section.OnPrint property:

Visual Basic	Copy Code
If SalespersonTotal > 5000 Then ExceededGoalLabel.Visible = True SalespersonLine.Visible = True Else ExceededGoalLabel.Visible = False SalespersonLine.Visible = False End If

Visual Basic

Copy Code

If SalespersonTotal > 5000 Then

ExceededGoalLabel.Visible = True

SalespersonLine.Visible = True

Else

ExceededGoalLabel.Visible = False

SalespersonLine.Visible = False

End If

C#	Copy Code
if (SalespersonTotal > 5000) { ExceededGoalLabel.Visible = true; SalespersonLine.Visible = true; } else { ExceededGoalLabel.Visible = false; SalespersonLine.Visible = false; }

Invoice

No action required.

Products by Category

No action required.

Sales by Category

This report contains a Chart control that is not imported. To add a chart to your report, you could use an unbound picture field, then write a VB event handler that would create the chart and assign it to the field as a picture.

Sales by Category Subreport

No action required.

Sales by Year

This report contains code and references to a Form object which need to be translated manually. To replace the Form object, edit the C1.C1Report.DataSource.RecordSource property to add a [Show Details] parameter:

Visual Basic	Copy Code
PARAMETERS (Beginning Date) DateTime 1/1/1994, (Ending Date) DateTime 1/1/2001, (Show Details) Boolean False; ...

C#	Copy Code
PARAMETERS [Beginning Date] DateTime 1/1/1994, [Ending Date] DateTime 1/1/2001, [Show Details] Boolean False; ...

Use the new parameter in the report's OnOpen event:

Visual Basic	Copy Code
Dim script As String = _ "bDetails = [Show Details]" & vbCrLf & _ "Detail.Visible = bDetails" & vbCrLf & _ "[Group 0 Footer].Visible = bDetails" & vbCrLf & _ "DetailsLabel.Visible = bDetails" & vbCrLf & _ "LineNumberLabel2.Visible = bDetails" & vbCrLf & _ "Line15.Visible = bDetails" & vbCrLf & _ "SalesLabel2.Visible = bDetails" & vbCrLf & _ "OrdersShippedLabel2.Visible = bDetails" & vbCrLf & _ "ShippedDateLabel2.Visible = bDetails" & vbCrLf & _ "Line10.Visible = bDetails" c1r.Sections.Detail.OnPrint = script

Visual Basic

Copy Code

Dim script As String = _
  "bDetails = [Show Details]" & vbCrLf & _
  "Detail.Visible = bDetails" & vbCrLf & _
  "[Group 0 Footer].Visible = bDetails" & vbCrLf & _
  "DetailsLabel.Visible = bDetails" & vbCrLf & _
  "LineNumberLabel2.Visible = bDetails" & vbCrLf & _
  "Line15.Visible = bDetails" & vbCrLf & _
  "SalesLabel2.Visible = bDetails" & vbCrLf & _
  "OrdersShippedLabel2.Visible = bDetails" & vbCrLf & _
  "ShippedDateLabel2.Visible = bDetails" & vbCrLf & _
  "Line10.Visible = bDetails"
c1r.Sections.Detail.OnPrint = script

C#	Copy Code
string script = "bDetails = [Show Details]" + "Detail.Visible = bDetails\r\n" + "[Group 0 Footer].Visible = bDetails\r\n" + "DetailsLabel.Visible = bDetails\r\n" + "LineNumberLabel2.Visible = bDetails\r\n" + "Line15.Visible = bDetails\r\n" + "SalesLabel2.Visible = bDetails\r\n" + "OrdersShippedLabel2.Visible = bDetails\r\n" + "ShippedDateLabel2.Visible = bDetails\r\n" + "Line10.Visible = bDetails"; c1r.Sections.Detail.OnPrint = script;

Copy Code

string script = "bDetails = [Show Details]" +  
  "Detail.Visible = bDetails\r\n" +  
  "[Group 0 Footer].Visible = bDetails\r\n" +  
  "DetailsLabel.Visible = bDetails\r\n" +  
  "LineNumberLabel2.Visible = bDetails\r\n" +  
  "Line15.Visible = bDetails\r\n" +  
  "SalesLabel2.Visible = bDetails\r\n" +  
  "OrdersShippedLabel2.Visible = bDetails\r\n" +  
  "ShippedDateLabel2.Visible = bDetails\r\n" +  
  "Line10.Visible = bDetails";
c1r.Sections.Detail.OnPrint = script;

Finally, two more lines of code need to be translated:

Visual Basic	Copy Code
Sections ("Detail").OnPrint = _ "PageHeader.Visible = True" Sections("Group 0 Footer).OnPrint = _ "PageHeader.Visible = False"

C#	Copy Code
Sections ("Detail").OnPrint = "PageHeader.Visible = true"; Sections("Group 0 Footer).OnPrint = "PageHeader.Visible = false";

Sales by Year Subreport

No action required.

Sales Totals by Amount

This report contains code that needs to be translated manually. The following code should be assigned to the Page Header C1.C1Report.Section.OnPrint property:

Visual Basic	Copy Code
PageTotal = 0

C#	Copy Code
PageTotal = 0;

The following code should be assigned to the Detail C1.C1Report.Section.OnPrint property:

Visual Basic	Copy Code
PageTotal = PageTotal + SaleAmount HiddenPageBreak.Visible = (Counter = 10)

C#	Copy Code
PageTotal = PageTotal + SaleAmount; HiddenPageBreak.Visible = (Counter = 10);

Summary of Sales by Quarter

This report has a group that is sorted by quarter (see item 4 above). To fix this, add a field to the source dataset that contains the value of the ShippedDate quarter, by changing the C1.C1Report.DataSource.RecordSource property as follows:

SELECT DISTINCTROW Orders.ShippedDate,

Orders.OrderID,

[Order Subtotals].Subtotal,

DatePart("q",Orders.ShippedDate) As ShippedQuarter

FROM Orders INNER JOIN [Order Subtotals]

ON Orders.OrderID = [Order Subtotals].OrderID

WHERE (((Orders.ShippedDate) Is Not Null));

Change the group's C1.C1Report.Group.GroupBy property to use the new field, ShippedQuarter.

Summary of Sales by Year

No action required.

Summing up the information on the table, out of the 13 reports imported from the NorthWind database: eight did not require any editing, three required some code translation, one required changes to the SQL statement, and one had a chart control that was not replaced.